Creating custom database indexes

Experienced database administrators can create or modify indexes to tune performance. Database indexes can improve application performance because they reduce the processing required for certain queries. Indexes should be used only after analyzing which SQL queries in an application are slow because of a database bottleneck.

Platform object records are stored in RB_OBJ_DATA table, which has more than 500 columns. Some of these columns are already indexed to improve performance of SQL queries. You can identify the indexed columns by searching the database creation scripts for lines including the CREATE INDEX command. For example:

CREATE INDEX RB_OBJ_DATA_I17 ON RB_OBJ_DATA(OBJ_DEF_ID, STR2);		

It is neither practical nor possible to index all columns in the RB_OBJ_DATA table. It is best to concentrate on columns with large numbers of records per object. Indexes can be created for single or multiple columns. For example, if your application uses a field mapped to a STR10 column to sort and filter large amount of records, you could create an index on that column. The following database command would create such an index:

CREATE INDEX RB_OBJ_DATA_CUST1 ON RB_OBJ_DATA(OBJ_DEF_ID, STR10);